﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using SQLServerDetectionControl;

namespace SQLServerBrowser
{
    public partial class SQLServerBrowser : Form
    {
        public SQLServerBrowser()
        {
            InitializeComponent();
            cmbWhereClause.SelectedIndex = 0;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void SQLServerBrowser_Load(object sender, EventArgs e)
        {
            cmbSelectColumn.DrawMode = DrawMode.OwnerDrawFixed;
            cmbSelectColumn.DrawItem += cmbSelectColumn_DrawItem;
            cmbSelectColumn.DropDownClosed += cmbSelectColumn_DropDownClosed;

            cmbColumnWhere.DrawMode = DrawMode.OwnerDrawFixed;
            cmbColumnWhere.DrawItem += cmbColumnWhere_DrawItem;
            cmbColumnWhere.DropDownClosed += cmbColumnWhere_DropDownClosed;

            cmbOrderBy.DrawMode = DrawMode.OwnerDrawFixed;
            cmbOrderBy.DrawItem += cmbOrderBy_DrawItem;
            cmbOrderBy.DropDownClosed += cmbOrderBy_DropDownClosed;

        }

        private void loginToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {

        }
        private string GetSelectTOP()
        {
            string SelectTOP = String.Empty;
            switch (cmbColumnCount.Text)
            {
                case "":
                    cmbColumnCount.SelectedIndex = 0;
                    SelectTOP = cmbColumnCount.Text;
                    break;
                case "All":
                    SelectTOP = String.Empty;
                    break;
                default:
                    SelectTOP = cmbColumnCount.Text;
                    break;
            }

            //if (rbAll.Checked)
            //    SelectTOP = String.Empty;
            //else if (rbTop10.Checked)
            //    SelectTOP = "TOP(10)"; 
            //else if (rbTop100.Checked)
            //    SelectTOP = "TOP(100)"; 
            //else if (rbTop1000.Checked)
            //    SelectTOP = "TOP(1000)";
            return SelectTOP;
        }

        private void btnExecute_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(cmbOrderBy.Text))
                LoadCombobox();

            SqlConnection theSqlServerConnection=null;
            string ConnectionString = string.Empty;

            try
            {
                ConnectionString = sqlServersDetector.DBConnectionString;
                theSqlServerConnection = new SqlConnection(ConnectionString);
                theSqlServerConnection.Open();
                String sSQL = String.Empty;
                String sSelectedColumn = String.Empty;
                foreach (String oItem in listBoxSelectedColumn.Items)
                {
                    sSelectedColumn += string.Format("{0}{1}", oItem.ToString(), ",");
                }
                if(listBoxSelectedColumn.Items.Count>0)
                    sSelectedColumn = sSelectedColumn.Substring(0, sSelectedColumn.Length - 1);
  
                String sWhereClause = String.Empty;
                foreach (String oItem in listBoxWhereClause.Items)
                {
                    sWhereClause += string.Format("{0}{1}", oItem.ToString(), " AND");
                }
                if (listBoxWhereClause.Items.Count > 0 && sWhereClause.EndsWith(" AND") )
                    sWhereClause = sWhereClause.Substring(0, sWhereClause.Length - 4);
                

                //SqlCommand theSqlCommand = new SqlCommand(String.Format("SELECT TOP(10) * FROM [{0}].[dbo].[{1}]", sqlServersDetector.SelectedDatabase, sqlServersDetector.SelectedTable), theSqlServerConnection);
                if (listBoxSelectedColumn.Items.Count == 0 && listBoxWhereClause.Items.Count == 0)
                    sSQL = String.Format("SELECT {0}* FROM [{1}].[dbo].[{2}] ORDER BY [{3}]", GetSelectTOP(), sqlServersDetector.SelectedDatabase, sqlServersDetector.SelectedTable, cmbOrderBy.Text);
                else if (listBoxSelectedColumn.Items.Count > 0 && listBoxWhereClause.Items.Count == 0)
                    sSQL = String.Format("SELECT {0} {4} FROM [{1}].[dbo].[{2}] ORDER BY [{3}]", GetSelectTOP(), sqlServersDetector.SelectedDatabase, sqlServersDetector.SelectedTable, cmbOrderBy.Text, sSelectedColumn);
                else if (listBoxSelectedColumn.Items.Count > 0 && listBoxWhereClause.Items.Count > 0)
                    sSQL = String.Format("SELECT {0} {1} FROM [{2}].[dbo].[{3}] WHERE {4} ORDER BY [{5}]"
                        , GetSelectTOP()
                        , sSelectedColumn
                        , sqlServersDetector.SelectedDatabase
                        , sqlServersDetector.SelectedTable
                        , sWhereClause
                        , cmbOrderBy.Text
                        );

                if (String.IsNullOrEmpty(sSQL))
                    throw new Exception("SQLQuery Empty!");

                SqlDataAdapter daDataAdapter = new SqlDataAdapter(sSQL, theSqlServerConnection);
                
                DataTable dtDataTable = new DataTable(sqlServersDetector.SelectedTable);
                daDataAdapter.Fill(dtDataTable);
                dataGridView.DataSource = dtDataTable;
                //txtRowCount.Text = Convert.ToString(dtDataTable.Rows.Count);
                theSqlServerConnection.Close();
            }
            catch (Exception Ex)
            {
                MessageBox.Show( Ex.Message );
            }
            finally
            {
                theSqlServerConnection.Close();
            }

            sqlServersDetector.GetColumnList(ref cmbColumnWhere);

            try
            {
                ConnectionString = sqlServersDetector.DBConnectionString;
                theSqlServerConnection = new SqlConnection(ConnectionString);
                String sSQL = String.Format("SELECT COUNT(*) FROM [{0}].[dbo].[{1}]", sqlServersDetector.SelectedDatabase, sqlServersDetector.SelectedTable);
                SqlCommand oSqlCommand = new SqlCommand(sSQL, theSqlServerConnection);
                oSqlCommand.Connection.Open();
                txtRowCount.Text = Convert.ToString(oSqlCommand.ExecuteScalar());
                //oSqlCommand.Connection.Close();
            }
            catch (Exception Ex)
            {
                MessageBox.Show( Ex.Message );
            }
            finally
            {
                theSqlServerConnection.Close();
            }

            
        }

        private void sqlServersDetector_Leave(object sender, EventArgs e)
        {
            txtRowCount.Text = Convert.ToString(sqlServersDetector.RecordCount);
        }

        private void btnListColumn_Click(object sender, EventArgs e)
        {
            LoadCombobox();
        }

        private void LoadCombobox()
        {
            if (cmbSelectColumn.Items.Count > 0)
                cmbSelectColumn.SelectedIndex = 0;
            sqlServersDetector.GetColumnList(ref cmbColumnWhere);
            sqlServersDetector.GetColumnList(ref cmbOrderBy);
            sqlServersDetector.GetColumnList(ref cmbSelectColumn);

        }

        private void listBoxSelectedColumn_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (listBoxSelectedColumn.SelectedItem != null)
            {
                cmbSelectColumn.Items.Add(listBoxSelectedColumn.SelectedItem);
                listBoxSelectedColumn.Items.Remove(listBoxSelectedColumn.SelectedItem);
                if (cmbSelectColumn.Items.Count > 0)
                    cmbSelectColumn.SelectedIndex = 0;
            }
        }

        private void btnAddSelectedColumn_Click(object sender, EventArgs e)
        {
            if (cmbSelectColumn.SelectedItem != null)
            {
                listBoxSelectedColumn.Items.Add(cmbSelectColumn.SelectedItem);
                cmbSelectColumn.Items.Remove(cmbSelectColumn.SelectedItem);
                if (cmbSelectColumn.Items.Count>0)
                    cmbSelectColumn.SelectedIndex  = 0;
            }
        }

        private void listBoxWhereClause_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (listBoxWhereClause.SelectedItem != null)
            {
                listBoxWhereClause.Items.Remove(listBoxWhereClause.SelectedItem);
            }
        }

        private void btnAddWhereClause_Click(object sender, EventArgs e)
        {
            if (cmbColumnWhere.SelectedItem != null && cmbWhereClause.SelectedItem != null && !String.IsNullOrEmpty(txtWhereClauseValue.Text.Trim()) )
            {
                listBoxWhereClause.Items.Add(String.Format("{0} {1} {2}", cmbColumnWhere.Text, cmbWhereClause.Text, txtWhereClauseValue.Text.Trim()));
            }
        }

        private void cmbSelectColumn_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0) { return; } // added this line thanks to Andrew's comment
            string text = cmbSelectColumn.GetItemText(cmbSelectColumn.Items[e.Index]);
            e.DrawBackground();
            using (SolidBrush br = new SolidBrush(e.ForeColor))
            { e.Graphics.DrawString(text, e.Font, br, e.Bounds); }
            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            { toolTip1.Show(text, cmbSelectColumn, e.Bounds.Right, e.Bounds.Bottom); }
            e.DrawFocusRectangle();
        }

        private void cmbSelectColumn_DropDownClosed(object sender, EventArgs e)
        {
            toolTip1.Hide(cmbSelectColumn);
        }
        private void cmbColumnWhere_DropDownClosed(object sender, EventArgs e)
        {
            toolTip1.Hide(cmbColumnWhere);
        }

        private void cmbColumnWhere_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0) { return; } // added this line thanks to Andrew's comment
            string text = cmbColumnWhere.GetItemText(cmbColumnWhere.Items[e.Index]);
            e.DrawBackground();
            using (SolidBrush br = new SolidBrush(e.ForeColor))
            { e.Graphics.DrawString(text, e.Font, br, e.Bounds); }
            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            { toolTip1.Show(text, cmbColumnWhere, e.Bounds.Right, e.Bounds.Bottom); }
            e.DrawFocusRectangle();
        }

        private void cmbOrderBy_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0) { return; } // added this line thanks to Andrew's comment
            string text = cmbOrderBy.GetItemText(cmbOrderBy.Items[e.Index]);
            e.DrawBackground();
            using (SolidBrush br = new SolidBrush(e.ForeColor))
            { e.Graphics.DrawString(text, e.Font, br, e.Bounds); }
            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            { toolTip1.Show(text, cmbOrderBy, e.Bounds.Right, e.Bounds.Bottom); }
            e.DrawFocusRectangle();
            
        }

        private void cmbOrderBy_DropDownClosed(object sender, EventArgs e)
        {
            toolTip1.Hide(cmbOrderBy);
        }
        
    }
}
